package dao.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import db.common.DBHelper;
import model.common.Employee;
import model.common.Goods;

public class GoodsDao {
	public void addGoods(Goods g) throws SQLException {
		Connection conn = DBHelper.getConnection();
		String sql = " "+"insert into tab_goods " + "(goodsName,goodsPrice,goodsManufacturer,"
				    + " goodsUnitPrice,goodsExpirationDate,goodsProductionDate,goodsStockDate) "
				    + " values(?,?,?,?,?,?,?)  ";
		
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, g.getGoodsName());
		ptmt.setString(2, g.getGoodsPrice());
		ptmt.setString(3, g.getGoodsManufacturer());
		ptmt.setString(4, g.getGoodsUnitPrice());
		ptmt.setDate(5, new java.sql.Date(g.getGoodsExpirationDate().getTime()));
		ptmt.setDate(6, new java.sql.Date(g.getGoodsProductionDate().getTime()));
		ptmt.setDate(7, new java.sql.Date(g.getGoodsStockDate().getTime()));
		
		ptmt.execute();
	}
	

	public void deleteGoods(Integer goodsId) throws SQLException {
		Connection conn = DBHelper.getConnection();
		String sql = " delete from tab_goods  where goodsId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, goodsId);
		ptmt.execute();
	}
	
	public void updateGoods(Goods g) throws SQLException{
		Connection conn=DBHelper.getConnection();
		String sql=" update tab_goods set goodsName=?,goodsPrice=?,goodsManufacturer=?,goodsProductionDate=?,"
		+ " goodsExpirationDate=?,goodsStockDate=?,goodsUnitPrice=?"
				+ " where goodsId=? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		
		ptmt.setString(1, g.getGoodsName());
		ptmt.setString(2, g.getGoodsPrice());
		ptmt.setString(3, g.getGoodsManufacturer());
		ptmt.setDate(4, new java.sql.Date(g.getGoodsProductionDate().getTime()));
		ptmt.setDate(5, new java.sql.Date(g.getGoodsExpirationDate().getTime()));
		ptmt.setDate(6, new java.sql.Date(g.getGoodsStockDate().getTime()));
		ptmt.setString(7, g.getGoodsUnitPrice());
		ptmt.setInt(8, g.getGoodsId());
		
		ptmt.execute();
		ptmt.close();
		conn.close();
	}
	
	public Goods get(Integer goodsId) throws SQLException {
		Goods g = null;
		Connection conn = DBHelper.getConnection();
		String sql = " select * from tab_goods where goodsId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, goodsId);
		ResultSet rs = ptmt.executeQuery();
		while (rs.next()) {
			g = new Goods();
			g.setGoodsId(rs.getInt("goodsId"));
			g.setGoodsName(rs.getString("goodsName"));
			g.setGoodsPrice(rs.getString("goodsPrice"));
			g.setGoodsManufacturer(rs.getString("goodsManufacturer"));
			g.setGoodsProductionDate(rs.getDate("goodsProductionDate"));
			g.setGoodsExpirationDate(rs.getDate("goodsExpirationDate"));
			g.setGoodsStockDate(rs.getDate("goodsStockDate"));
			g.setGoodsUnitPrice(rs.getString("goodsUnitPrice"));
		}
		rs.close();
		ptmt.close();
		conn.close();
		return g;
	}
	
	public static void main(String[] args) throws SQLException{
		Goods goods = new GoodsDao().get(1);
	}
	public List<Goods> query(List<Map<String, Object>> params) throws SQLException{
		List<Goods> result=new ArrayList<Goods>(); 
		Connection conn = DBHelper.getConnection();
		StringBuffer sb=new StringBuffer();
		sb.append("select * from tab_goods where 1=1 ");
		if(params!=null&&params.size()>0){
			for(int i=0;i<params.size();i++){
				Map<String, Object> map=params.get(i);
				sb.append(" and " +map.get("name")+"  " +map.get("rela")+"  " +map.get("value"));
			}
		}
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());

		System.out.println(sb.toString());
		ResultSet rs=ptmt.executeQuery();
		
		Goods g = null;
		while (rs.next()) {
		g=new Goods();
		g = new Goods();
		g.setGoodsId(rs.getInt("goodsId"));
		g.setGoodsPrice(rs.getString("goodsPrice"));
		g.setGoodsManufacturer(rs.getString("goodsManufacturer"));
		g.setGoodsProductionDate(rs.getDate("goodsProductionDate"));
		g.setGoodsExpirationDate(rs.getDate("goodsExpirationDate"));
		g.setGoodsStockDate(rs.getDate("goodsStockDate"));
		g.setGoodsUnitPrice(rs.getString("goodsUnitPrice"));
			result.add(g);
		}
		
		return result;
		}
		
	public List<Goods> query() throws Exception{
		List<Goods> result=new ArrayList<Goods>();
		
		Connection conn=DBHelper.getConnection();
		StringBuilder sb=new StringBuilder();
		sb.append("select goodsId,goodsName,goodsPrice from tab_goods ");
		
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());
		
		ResultSet rs=ptmt.executeQuery();
		
		Goods g=null;
		while(rs.next()){
			g=new Goods();
			g.setGoodsId(rs.getInt("goodsId"));
			g.setGoodsName(rs.getString("goodsName"));
			g.setGoodsPrice(rs.getString("goodsPrice"));
			result.add(g);
		}
		return result;
	}
	
	public List<Goods> query(String goodsName,String goodsPrice) throws Exception{
		List<Goods> result=new ArrayList<Goods>();
		
		Connection conn=DBHelper.getConnection();
		StringBuilder sb=new StringBuilder();
		sb.append("select * from tab_goods  ");
		
		sb.append(" where goodsName like ? and goodsPrice like ? ");
		
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());
		ptmt.setString(1, "%"+goodsName+"%");
		ptmt.setString(2, "%"+goodsPrice+"%");
		
		System.out.println(sb.toString());
		ResultSet rs=ptmt.executeQuery();
		
		Goods g=null;
		while(rs.next()){
			g=new Goods();
			g.setGoodsId(rs.getInt("goodsId"));
			g.setGoodsName(rs.getString("goodsName"));
			g.setGoodsPrice(rs.getString("goodsPrice"));
			g.setGoodsManufacturer(rs.getString("goodsManufacturer"));
			g.setGoodsProductionDate(rs.getDate("goodsProductionDate"));
			g.setGoodsExpirationDate(rs.getDate("goodsExpirationDate"));
			g.setGoodsStockDate(rs.getDate("goodsStockDate"));
			g.setGoodsUnitPrice(rs.getString("goodsUnitPrice"));
			result.add(g);
		}
		return result;
	}


}
